/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Handlers;

import gui.Main;
import gui.Main;
import ObjectClass.Certificate;
import ObjectClass.Driver;
import ObjectClass.DriverCertificate;
import ObjectClass.SQLCommunication;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
 
/**
 *
 * @author Jeppe
 */
public class DriverCertificateHandler {

    private final static String TABEL = "DriverCertificate";
    private Main main;
    private SQLCommunication sqlc;
    private DriverHandler driverHandler;
    private CertificateHandler certificateHandler;
    private TaskHandler taskHandler;
    private ArrayList<DriverCertificate> driverCertificateList;

    public DriverCertificateHandler(Main main) {
        this.main = main;
        this.sqlc = main.getSql();
        this.driverHandler = main.getDriverHandler();
        this.certificateHandler = main.getCertificateHandler();
        this.taskHandler = main.getTaskHandler();
        driverCertificateList = new ArrayList<>();
    }

    private void instantierDriverCetificates() {
        driverCertificateList.clear();
        Driver aDriver;
        Certificate aCertificate;

        String sql = "SELECT * FROM " + TABEL;
        ResultSet rs = sqlc.select(TABEL, sql);
        try {
            while (rs.next()) {
                aDriver = driverHandler.getDriver(rs.getInt("DriverPhoneNo"));
                aCertificate = certificateHandler.getCertificateByID(rs.getInt("CertificateID"));

                DriverCertificate driverCertificate = new DriverCertificate(aDriver, aCertificate);
                driverCertificateList.add(driverCertificate);
            }
        } catch (SQLException ex) {
            System.out.println("Der er sket en fejl i forbindelse med instantiering af certifikatList");
        }       
    }

    public ArrayList<DriverCertificate> getDriverCertificateList() {
        instantierDriverCetificates();
        return driverCertificateList;
    }

    public ArrayList<Certificate> getCertificateListByDriver(Driver driver) {
        ArrayList<Certificate> demandCertificateList = new ArrayList<>();
        for (int i = 0; i < driverCertificateList.size(); i++) {
            if (driverCertificateList.get(i).getDriver().getPhoneNo() == driver.getPhoneNo()) {
                demandCertificateList.add(driverCertificateList.get(i).getCertificate());
            }
        }
        return demandCertificateList;
    }

    public ArrayList<Driver> getDriverListByCertificateNY(boolean ADRCertificate, boolean craneCertificate, boolean EUCertificate, int dd, int mm, int YYYY) {
        ArrayList<Driver> demandDriverList = new ArrayList<>();
        boolean removed = false;
        for (int i = 0; i < driverHandler.getDriverList().size(); i++) {
            demandDriverList.add(driverHandler.getDriverList().get(i));
        }

        for (int i = 0; i < demandDriverList.size(); i++) {
//            System.out.println("DEMAND: "+i);
            boolean ADR = false;
            boolean crane = false;
            boolean EU = false;
            if (ADRCertificate) {
                System.out.println("ADR certifikat er nødvendigt");
                String sql = "select * from drivercertificate,driver where DriverPhoneNo = " + demandDriverList.get(i).getPhoneNo() + " and CertificateID = 1 and DriverPhoneNo = driver.PhoneNo;";
                ResultSet rs = sqlc.select(TABEL, sql);
                try {
                    if (rs.next()) {
                        ADR = true;
                    }
                } catch (SQLException ex) {
                    System.out.println("Tjek af ADRCertifikat gik galt");
                }
            }
            if (craneCertificate) {
                System.out.println("Kran certifikat er nødvendigt");
                String sql = "select * from drivercertificate,driver where DriverPhoneNo = " + demandDriverList.get(i).getPhoneNo() + " and CertificateID = 2 and DriverPhoneNo = driver.PhoneNo;";
                ResultSet rs = sqlc.select(TABEL, sql);
                try {
                    if (rs.next()) {
                        crane = true;
                    }
                } catch (SQLException ex) {
                    System.out.println("Tjek af krancertifikat gik galt");
                }
            }
            if (EUCertificate) {
                System.out.println("EU ertifikat er nødvendigt");
                String sql = "select * from drivercertificate,driver where DriverPhoneNo = " + demandDriverList.get(i).getPhoneNo() + " and CertificateID = 3 and DriverPhoneNo = driver.PhoneNo;";
                ResultSet rs = sqlc.select(TABEL, sql);
                try {
                    if (rs.next()) {
                        EU = true;
                    }
                } catch (SQLException ex) {
                    System.out.println("Tjek af EUcertifikat gik galt");
                }
            }

            removed = false;
            if (ADRCertificate && !removed) {
                if (!ADR) {
                    System.out.println(demandDriverList.get(i).toString() + " bliver fjernet pga. manglende ADR-Certificate");
                    demandDriverList.remove(i);
                    removed = true;
                }
            }
            if (craneCertificate&& !removed) {
                if (!crane) {
                    System.out.println(demandDriverList.get(i).toString() + " bliver fjernet pga. manglende CRANE-Certificate");
                    demandDriverList.remove(i);
                    removed = true;
                }
            }
            if (EUCertificate && !removed) {
                if (!EU) {
                    System.out.println(demandDriverList.get(i).toString() + " bliver fjernet pga. manglende EU-Certificate");
                    demandDriverList.remove(i);
                    removed = true;
                }
            }

        }
        return demandDriverList;
    }
    
//    public ArrayList<Driver> getDriverListByCertifikateVirkerMaybe(boolean ADRCertificate, boolean craneCertificate, boolean EUCertificate, int dd, int mm, int YYYY){
//        ArrayList<Driver> demandDriverList = new ArrayList<>();
//        
//            boolean ADR = false;
//            boolean crane = false;
//            boolean EU = false;
//            if (ADRCertificate) {
//                System.out.println("ADR certifikat er nødvendigt");
//                String sql = "select * from drivercertificate,driver where DriverPhoneNo = " + demandDriverList.get(i).getPhoneNo() + " and CertificateID = 1 and DriverPhoneNo = driver.PhoneNo;";
//                ResultSet rs = sqlc.select(TABEL, sql);
//                try {
//                    if (rs.next()) {
//                        ADR = true;
//                    }
//                } catch (SQLException ex) {
//                    System.out.println("Tjek af ADRCertifikat gik galt");
//                }
//            }
//            if (craneCertificate) {
//                System.out.println("Kran certifikat er nødvendigt");
//                String sql = "select * from drivercertificate,driver where DriverPhoneNo = " + demandDriverList.get(i).getPhoneNo() + " and CertificateID = 2 and DriverPhoneNo = driver.PhoneNo;";
//                ResultSet rs = sqlc.select(TABEL, sql);
//                try {
//                    if (rs.next()) {
//                        crane = true;
//                    }
//                } catch (SQLException ex) {
//                    System.out.println("Tjek af krancertifikat gik galt");
//                }
//            }
//            if (EUCertificate) {
//                System.out.println("EU ertifikat er nødvendigt");
//                String sql = "select * from drivercertificate,driver where DriverPhoneNo = " + demandDriverList.get(i).getPhoneNo() + " and CertificateID = 3 and DriverPhoneNo = driver.PhoneNo;";
//                ResultSet rs = sqlc.select(TABEL, sql);
//                try {
//                    if (rs.next()) {
//                        EU = true;
//                    }
//                } catch (SQLException ex) {
//                    System.out.println("Tjek af EUcertifikat gik galt");
//                }
//            }
//
//            if (ADRCertificate) {
//                    System.out.println(demandDriverList.get(i).toString() + " bliver fjernet pga. manglende ADR-Certificate");
//                
//            }
//            if (craneCertificate) {
//                
//            }
//            if (EUCertificate) {
//               
//            }
//
//        
//        return demandDriverList;
//    }
    public ArrayList<Driver> getDriverListByCertificate(boolean ADRCertificate, boolean craneCertificate, boolean EUCertificate, int dd, int mm, int YYYY) {
        ArrayList<Driver> demandDriverList = new ArrayList<>();
        taskHandler = main.getTaskHandler();
        boolean ADR = ADRCertificate;
        boolean crane = craneCertificate;
        boolean EU = EUCertificate;
        for (int i = 0; i < driverCertificateList.size(); i++) {

            if (ADRCertificate) {
                if (driverCertificateList.get(i).getCertificate().getID() == 1) {
                    for (int j = 0; j < taskHandler.getTaskListByDato(dd, mm, YYYY).size(); j++) {
                        if (taskHandler.getTaskListByDato(dd, mm, YYYY).get(j).getDriver().getPhoneNo() != driverCertificateList.get(i).getDriver().getPhoneNo()) {
                            ADR = true;
                        } else {
                            ADR = false;
                        }
                    }
                }
            }
            if (craneCertificate) {
                if (driverCertificateList.get(i).getCertificate().getID() == 2) {
                    for (int j = 0; j < taskHandler.getTaskListByDato(dd, mm, YYYY).size(); j++) {
                        if (taskHandler.getTaskListByDato(dd, mm, YYYY).get(j).getDriver().getPhoneNo() != driverCertificateList.get(i).getDriver().getPhoneNo()) {
                            crane = true;
                        } else {
                            crane = false;
                        }
                    }
                }
            }
            if (EUCertificate) {
                if (driverCertificateList.get(i).getCertificate().getID() == 3) {
                    for (int j = 0; j < taskHandler.getTaskListByDato(dd, mm, YYYY).size(); j++) {
                        if (taskHandler.getTaskListByDato(dd, mm, YYYY).get(j).getDriver().getPhoneNo() != driverCertificateList.get(i).getDriver().getPhoneNo()) {
                            EU = true;
                        } else {
                            EU = false;
                        }
                    }
                }
            }
            if (ADR == ADRCertificate && crane == craneCertificate && EU == EUCertificate) {
                boolean exists = false;
                for (int j = 0; j < demandDriverList.size(); j++) {
                    if (demandDriverList.get(j).getPhoneNo() != driverCertificateList.get(i).getDriver().getPhoneNo()) {
                        exists = true;
                    }
                }
                if (!exists) {
                    demandDriverList.add(driverCertificateList.get(i).getDriver());
                }
            }
        }
        return demandDriverList;
    }

    public boolean tjekDriverCertificate(DriverCertificate driverCertificate) {
        boolean exists = false;
        for (int i = 0; i < driverCertificateList.size(); i++) {
            if (driverCertificateList.get(i).getCertificate().getID() == driverCertificate.getCertificate().getID() && driverCertificateList.get(i).getDriver().getPhoneNo() == driverCertificate.getDriver().getPhoneNo()) {
                exists = true;
            }
        }
        return exists;
    }

    public void insertToDB(DriverCertificate driverCertificate) {
        String sql;
        sql = "insert into " + TABEL + " " + driverCertificate.toStringSql();
        if (tjekDriverCertificate(driverCertificate)) {
            System.out.println("Det drivercertificate findes allerede i Databasen");
        } else {
            sqlc.insert(TABEL, sql);
            System.out.println("Drivercertificate er indsat");
        }
    }
}
